# !/usr/bin/python3
# -*- coding:utf-8 -*-
"""
@author: JHC000abc@gmail.com
@file: util_excel.py
@time: 2025/3/12 14:40 
@desc: 

"""
import copy
import pandas as pd
from typing import List, Dict
import xlsxwriter
from utils.util_imgs import ImagesMerge


class Excel(object):
    """

    """

    def read_yield_by_pandas(self, file, headers=None, sheets=None):
        """

        :param file:
        :param headers:
        :param sheets:[]
        :return:
        """
        headers_recode = copy.deepcopy(headers)
        if not sheets:
            excel_file = pd.ExcelFile(file)
            sheets = excel_file.sheet_names
        for sheet in sheets:
            headers = headers_recode
            num = 0
            df = pd.read_excel(file, header=None, sheet_name=sheet)
            for row in df.itertuples(index=None, name=None):
                if num == 0:
                    num += 1
                    if not headers:
                        headers = row
                        continue
                yield {
                    "sheet": sheet,
                    "headers": list(headers),
                    "num": num,
                    "line": [str(i) for i in row],
                }
                num += 1

    @staticmethod
    def write(file: str, data: List, headers: List, sheets=None, style_header: Dict = None):
        """
        支持同时写入多个sheet
        向已经存在的excel表中写入数据时会覆盖原有数据
        :param file:
        :param data:[[[],[],[]]] 三层
        :param headers:[[],[],[]] 二层
        :param sheets:[,,,] 一层
        :param style_header:{
                        'font_name': '微软雅黑',
                        'font_size': 18,
                        'bold': True,
                        'border': 0,
                        'align': 'center',
                        'valign': 'vcenter',
                        'text_wrap': False,
                        "color":"white",
                        "bg_color":"red",
                        }
        :return:
        """
        if sheets is None:
            sheets = ["Sheet1"]
        if len(data) != len(sheets) or len(headers) != len(sheets):
            raise ValueError("data,headers,sheets长度不一致")
        # 全局样式
        options = {
            'strings_to_numbers': False,
            'strings_to_urls': True,
            'constant_memory': False,
            'default_format_properties': {
                'font_name': '微软雅黑',
                'font_size': 11,
                'bold': False,
                'border': 0,
                'align': 'left',
                'valign': 'vcenter',
                'text_wrap': False,
            },
        }
        workbook = xlsxwriter.Workbook(file, options)
        for index, sheet in enumerate(sheets):
            worksheet = workbook.add_worksheet(sheet)
            data[index].insert(0, headers[index])
            for row, lis in enumerate(data[index]):
                # print("lis",lis)
                for col, val in enumerate(lis):
                    # header 样式
                    if row == 0:
                        cell_format = workbook.add_format(style_header)
                    else:
                        cell_format = None
                    # print("----", headers, index, row, col, val)
                    if headers[index][col] == "首页图片":
                        if row > 0:
                            if val != "":
                                worksheet.insert_image(
                                    row=row,
                                    col=col,
                                    filename=val,
                                    options={
                                        'image_data': ImagesMerge().get_img_date(val),
                                        'x_scale': 0.1,  # 图片水平缩放（按比例缩放）
                                        'y_scale': 0.1,  # 图片垂直缩放（按比例缩放）
                                        'object_position': 1  # 设置图片嵌入单元格
                                    }
                                )
                            else:
                                worksheet.write_string(
                                    row=row,
                                    col=col,
                                    string=str(val),
                                    cell_format=cell_format,
                                )
                        else:
                            worksheet.write_string(
                                row=row,
                                col=col,
                                string=str(val),
                                cell_format=cell_format,
                            )
                    else:
                        worksheet.write_string(
                            row=row,
                            col=col,
                            string=str(val),
                            cell_format=cell_format,
                        )

        workbook.close()
